package com.boss.thirteen.dao.impl;


import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.entity.SalaryPaymentRequisitionBack;
import com.boss.sys.utils.SysUtils;
import com.boss.thirteen.dao.IThirteenGenerateSalaryDataDao;
import com.boss.thirteen.entity.ThirteenAgencyVerify;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class ThirteenGenerateSalaryDataDaoImpl extends BaseDaoImpl implements IThirteenGenerateSalaryDataDao {


    @Override
    public List<Map<String, Object>> getPersonOtherSalary(String agencyId, List<String> columnName) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" av.agency_id id, av.agency_code, av.agency_name,  sme.merit_pay_code, sme.sala_card_bank ");
        if (!StringUtils.isEmpty(columnName) && columnName.size() > 0) {
            for (String column : columnName) {
                sql.append(" ,sum(").append(column).append(") ").append(column);
            }
        }
        sql.append(" FROM ").append(TableName.SALARY_THIRTEEN_MONTH).append(" sme ")
                .append(" JOIN ").append(TableName.THIRTEEN_AGENCY_VERIFY).append(" av ")
                .append(" ON sme.agency_id = av.agency_id  ")
                .append(" where ").append(" av.is_pay_data_created = ? ")
                .append(" AND sme.is_deleted = 0")
                .append(" AND av.is_end = ? ");
        sql.append(" and av.agency_id = ?");
        sql.append(" and av.is_deleted = ?");
        sql.append(" GROUP BY av.agency_id, av.agency_code, av.agency_name, sme.sala_card_bank, sme.merit_pay_code ");
        sql.append(" ORDER BY agency_code,sme.sala_card_bank ");
        paramsList.add(SystemDictConstant.IS_FALSE);
        paramsList.add(SystemDictConstant.IS_FALSE);
        paramsList.add(agencyId);
        paramsList.add(SystemDictConstant.NOT_DELETED);
        return this.getjdbcTemplate().queryForList(sql.toString(), paramsList.toArray());
    }

    @Override
    public List<Map<String, Object>> getPayeeColumnName(List<String> operateAgencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT sas.salary_code column_name, ssbi.salary_base_id, ssbi.person_state, ssbi.agency_id, ")
                .append(" spa.payee_acct_name, spa.payee_acct_no, spa.payee_acct_bank_name,ssbi.remark ");
        sql.append(" FROM ").append(TableName.SALARY_BASEDATA).append(" sas ")//原取值逻辑为从单位工资项挂接表取数
                .append(" JOIN ").append(TableName.THIRTEEN_SALARY_ITEMIZE).append(" ssbi ")
                .append(" ON  sas.id = ssbi.salary_base_id  ")
                .append(" JOIN ").append(TableName.PAYEE_ACCT).append(" spa ")
                .append(" on ssbi.payee_acct_id = spa.id and spa.is_deleted = 0");
        sql.append(" WHERE sas.is_deleted = 0 and ssbi.is_deleted = 0 ")
                .append(" and ").append(SysUtils.inSql("ssbi.agency_id", operateAgencyIds));
        sql.append(" ORDER BY ssbi.agency_id ");
        return this.getjdbcTemplate().queryForList(sql.toString());
    }



    @Override
    public void updatePayData(String agencyId, String payFlag) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.THIRTEEN_AGENCY_VERIFY);
        sql.append(" set is_pay_data_created = ?,payment_create_time=? where agency_id = ? and is_end = ? and is_deleted=? ");
        Object[] objects = new Object[] {
                payFlag,String.valueOf(DateUtils.getCurrentTimeStamp()), agencyId, SystemDictConstant.IS_FALSE,SystemDictConstant.NOT_DELETED
        };
        this.getjdbcTemplate().update(sql.toString(), objects);
    }

    @Override
    public void updatePayDatas(List<String> agencyIds, String payFlag,String paymentCreateTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.THIRTEEN_AGENCY_VERIFY);
        sql.append(" set is_pay_data_created = ?,payment_create_time=? where ").append(SysUtils.inSql("agency_id",agencyIds))
                .append(" and is_end = ? and is_deleted=? ");
        Object[] objects = new Object[] {
                payFlag,paymentCreateTime, SystemDictConstant.IS_FALSE,SystemDictConstant.NOT_DELETED
        };
        this.getjdbcTemplate().update(sql.toString(), objects);
    }

    @Override
    public void insertPayBackData(SalaryPaymentRequisitionBack salaryPaymentRequisitionBack) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.THIRTEEN_PAYMENT_BACK);
        sql.append("(id, biz_key, create_time, update_time, agency_id, agency_name, pay_id, pay_app_no, ")
                .append(" bgt_id, pay_app_amt, fund_type, payee_acct_name, payee_acct_no, payee_acct_bank_name, ")
                .append(" pay_type, pro_cat, gov_bgt_eco, dep_bgt_eco, pro, use_name, exp_func, is_deleted,mof_div_code )");
        sql.append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        Object[] objects = new Object[]{
                salaryPaymentRequisitionBack.getId(), salaryPaymentRequisitionBack.getBizKey(), salaryPaymentRequisitionBack.getCreateTime(),
                salaryPaymentRequisitionBack.getUpdateTime(), salaryPaymentRequisitionBack.getAgencyId(),
                salaryPaymentRequisitionBack.getAgencyName(), salaryPaymentRequisitionBack.getPayId(), salaryPaymentRequisitionBack.getPayAppNo(),
                salaryPaymentRequisitionBack.getBgtId(),
                salaryPaymentRequisitionBack.getPayAppAmt(), salaryPaymentRequisitionBack.getFundType(), salaryPaymentRequisitionBack.getPayeeAcctName(),
                salaryPaymentRequisitionBack.getPayeeAcctNo(), salaryPaymentRequisitionBack.getPayeeAcctBankName(), salaryPaymentRequisitionBack.getPayType(),
                salaryPaymentRequisitionBack.getProCat(), salaryPaymentRequisitionBack.getGovBgtEco(), salaryPaymentRequisitionBack.getDepBgtEco(),
                salaryPaymentRequisitionBack.getPro(), salaryPaymentRequisitionBack.getUseName(), salaryPaymentRequisitionBack.getExpFunc(),
                salaryPaymentRequisitionBack.getIsDeleted(),salaryPaymentRequisitionBack.getMofDivCode()
        };
        this.getjdbcTemplate().update(sql.toString(), objects);
    }

    @Override
    public void batchInsertPayBackData(List<SalaryPaymentRequisitionBack> salaryPaymentRequisitionBacks) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.THIRTEEN_PAYMENT_BACK);
        sql.append("(id, biz_key, create_time, update_time, agency_id, agency_name, pay_id, pay_app_no, ")
                .append(" bgt_id, pay_app_amt, fund_type, payee_acct_name, payee_acct_no, payee_acct_bank_name, ")
                .append(" pay_type, pro_cat, gov_bgt_eco, dep_bgt_eco, pro, use_name, exp_func, is_deleted,mof_div_code )");
        sql.append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        List<Object[]> paramList = new ArrayList<>(salaryPaymentRequisitionBacks.size());
        for(SalaryPaymentRequisitionBack salaryPaymentRequisitionBack : salaryPaymentRequisitionBacks){
            Object[] objects = new Object[]{
                    salaryPaymentRequisitionBack.getId(), salaryPaymentRequisitionBack.getId(), salaryPaymentRequisitionBack.getCreateTime(),
                    salaryPaymentRequisitionBack.getUpdateTime(), salaryPaymentRequisitionBack.getAgencyId(),
                    salaryPaymentRequisitionBack.getAgencyName(), salaryPaymentRequisitionBack.getPayId(), salaryPaymentRequisitionBack.getPayAppNo(),
                    salaryPaymentRequisitionBack.getBgtId(),
                    salaryPaymentRequisitionBack.getPayAppAmt(), salaryPaymentRequisitionBack.getFundType(), salaryPaymentRequisitionBack.getPayeeAcctName(),
                    salaryPaymentRequisitionBack.getPayeeAcctNo(), salaryPaymentRequisitionBack.getPayeeAcctBankName(), salaryPaymentRequisitionBack.getPayType(),
                    "", salaryPaymentRequisitionBack.getGovBgtEco(), salaryPaymentRequisitionBack.getDepBgtEco(),
                    salaryPaymentRequisitionBack.getPro(), salaryPaymentRequisitionBack.getUseName(), salaryPaymentRequisitionBack.getExpFunc(),
                    salaryPaymentRequisitionBack.getIsDeleted(),salaryPaymentRequisitionBack.getMofDivCode()
            };
            paramList.add(objects);
        }

        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramList.get(i);
                for(int a = 0; a < sme.length; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void deletePayBackData(SalaryPaymentRequisitionBack salaryPaymentRequisitionBack) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.THIRTEEN_PAYMENT_BACK).append(" set ");
        sql.append(" is_deleted = 1 where id = ? ");
        Object[] objects = new Object[] {
                salaryPaymentRequisitionBack.getId()
        };
        this.getjdbcTemplate().update(sql.toString(), objects);
    }

    @Override
    public List<SalaryPaymentRequisitionBack> getPayBackData(Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT sprb.* FROM ")
                .append(TableName.THIRTEEN_AGENCY_VERIFY).append(" av ")
                .append(" JOIN ").append(TableName.THIRTEEN_PAYMENT_BACK).append(" sprb ")
                .append(" ON av.agency_id = sprb.agency_id ");
        sql.append(" WHERE av.agency_id = ? and av.is_end = ? and sprb.is_deleted = ? AND av.is_deleted=? ");
        Object[] objects = new Object[]{
                queryParam.get("agencyId"), SystemDictConstant.IS_FALSE, SystemDictConstant.NOT_DELETED, SystemDictConstant.NOT_DELETED
        };
        return this.getjdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryPaymentRequisitionBack.class), objects);
    }

    @Override
    public List<Map<String, Object>> getRevokePayBackData(List<String> operateAgencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT sprb.id, sprb.pay_id, av.agency_id ").append(" FROM ")
                .append(TableName.THIRTEEN_AGENCY_VERIFY).append(" av ")
                .append(" JOIN ").append(TableName.THIRTEEN_PAYMENT_BACK).append(" sprb ")
                .append(" ON av.agency_id = sprb.agency_id ");
        sql.append(" where av.is_end = 0  and sprb.is_deleted=0 and av.is_deleted=0 ");
        sql.append(" and ").append(SysUtils.inSql("av.agency_id", operateAgencyIds));
        return this.getjdbcTemplate().queryForList(sql.toString());
    }

    @Override
    public List<ThirteenAgencyVerify> getBankDataStatus(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" id, biz_key, mould_key, create_time, update_time, agency_id, salary_info_verified_code, ");
        sql.append(" is_submit_in_agency, is_salary_submit, is_workflow_init, is_end, is_deleted, audit_explain, ");
        sql.append(" batch, is_pay_data_created, is_bank_data_created, mof_div_code, salary_year year, is_verify_end, agency_code, agency_name, payment_create_time ");
        sql.append(" FROM ").append(TableName.THIRTEEN_AGENCY_VERIFY);
        sql.append(" where is_end = ? and is_pay_data_created = ? and is_deleted=? ");
        sql.append(" and ").append(SysUtils.inSql("agency_id", agencyIds));
        Object[] objects = new Object[]{
                SystemDictConstant.IS_FALSE, SystemDictConstant.IS_TRUE, SystemDictConstant.NOT_DELETED
        };
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(ThirteenAgencyVerify.class), objects);
    }



    @Override
    public List<Map<String, Object>> isPaymentAllConnectPersonType(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
                .append(" d.agency_id id,d.agency_name,c.name person_type,e.salary_base_name from ")
                .append("( select distinct agency_id,merit_pay_code,mof_div_code from   ")
                .append(TableName.SALARY_THIRTEEN_MONTH)
                .append(" where ").append(SysUtils.inSql("agency_id", agencyIds))
                .append(" and is_deleted=0 ")
                .append(") as  b ")
                .append(" join ")
                .append(TableName.BASE_DATA).append(" as c ")
                .append("on b.merit_pay_code=c.code and b.mof_div_code=c.mof_div_code ")
                .append(" join ")
                .append(TableName.THIRTEEN_AGENCY_VERIFY).append(" as  d ")
                .append("on b.agency_id=d.agency_id and d.is_end=0 and d.is_deleted=").append(SystemDictConstant.NOT_DELETED)
                .append(" join ")
                .append(TableName.THIRTEEN_SALARY_ITEMIZE).append(" as e ")
                .append("ON e.agency_id = b.agency_id and e.person_state=b.merit_pay_code")
                .append(" join ")
                .append(TableName.SAL_PERSON_TYPE_SALARY).append(" as f ")
                .append(" on e.salary_base_id=f.salary_id and b.merit_pay_code=f.person_type_code ")
                .append(" left join ")
                .append(TableName.THIRTEEN_SALARY_BGT).append(" as a ")
                .append(" on a.salary_year=d.salary_year and a.is_using=1 and a.agency_id=b.agency_id and a.person_type=b.merit_pay_code and a.salary_id=f.salary_id ")
                .append(" and a.is_deleted=").append(SystemDictConstant.NOT_DELETED)
                .append(" where a.id is null and e.is_deleted=").append(SystemDictConstant.NOT_DELETED);
        return this.getjdbcTemplate().queryForList(sql.toString());
    }
    @Override
    public List<Map<String, Object>> isSalaryAllConnectPersonType(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
                .append(" d.id,d.agency_name,c.name person_type from ")
                .append("( select distinct agency_id,merit_pay_code,mof_div_code from   ")
                .append(TableName.SALARY_THIRTEEN_MONTH)
                .append(" where ").append(SysUtils.inSql("agency_id", agencyIds))
                .append(" and is_deleted=0 ")
                .append(") as  b ")
                .append(" join ")
                .append(TableName.BASE_DATA).append(" as c ")
                .append("on b.merit_pay_code=c.code and b.mof_div_code=c.mof_div_code ")
                .append(" join ")
                .append(TableName.AGENCY_INFO).append(" as  d ")
                .append("on b.agency_id=d.id ")
                .append(" left join ")
                .append(TableName.THIRTEEN_SALARY_ITEMIZE).append(" as a ")
                .append(" on a.agency_id=b.agency_id and a.person_state=b.merit_pay_code and a.is_deleted=? ")
                .append(" where a.id is null ");
                Object[] objects=new Object[]{
                    SystemDictConstant.NOT_DELETED
                };

        return this.getjdbcTemplate().queryForList(sql.toString(),objects);
    }
    @Override
    public List<Map<String,Object>> getPersonType(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
                .append(" distinct merit_pay_code,agency_id from ")
                .append(TableName.SALARY_THIRTEEN_MONTH).append(" where ").append(SysUtils.inSql("agency_id", agencyIds));


        return this.getjdbcTemplate().queryForList(sql.toString());
    }
    /** 新支付推送代码20210426新增*/
    @Override
    public List<String> getPayeeColumnName(String province) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT distinct sbd.salary_code ");

        sql.append(" FROM ").append(TableName.THIRTEEN_SALARY_ITEMIZE).append(" as sbi ")
        .append(" join ").append(TableName.SALARY_BASEDATA).append(" as sbd")
                .append(" on sbi.salary_base_id = sbd.id ")
        .append(" WHERE  sbi.is_deleted = 0 and sbd.is_deleted = 0 ")
//                .append(" and salary_base_code is not null ")
                .append(" and sbi.mof_div_code=? ");
        return super.getjdbcTemplate().queryForList(sql.toString(), String.class, province);
    }

    @Override
    public List<Map<String, Object>> getPersonOtherSalary(List<String> agencyIds, List<String> columnName,boolean is_agency) {
        List<Object> paramsList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" av.agency_id, av.agency_code, av.agency_name, sme.merit_pay_code, sme.sala_card_bank ");
        if (!StringUtils.isEmpty(columnName) && columnName.size() > 0) {
            for (String column : columnName) {
                sql.append(" ,sum( nvl(").append(column).append(",0)) ").append(column);
            }
        }
        
        sql.append(" FROM ").append(TableName.SALARY_THIRTEEN_MONTH).append(" sme ")
                .append(" JOIN ").append(TableName.THIRTEEN_AGENCY_VERIFY).append(" av ")
                .append(" ON sme.agency_id = av.agency_id  ");

        sql.append(" where ").append(" av.is_pay_data_created = ? ")
                .append(" AND sme.is_deleted = ? and av.is_deleted=? ")
                .append(" AND av.is_end = ? ");
        sql.append(" and ").append(SysUtils.inSql("av.agency_id",agencyIds));
        sql.append(" GROUP BY av.agency_id, av.agency_code, av.agency_name, sme.sala_card_bank, sme.merit_pay_code ");
        sql.append(" ORDER BY agency_code, sme.sala_card_bank ");
        paramsList.add(SystemDictConstant.IS_FALSE);
        paramsList.add(SystemDictConstant.NOT_DELETED);
        paramsList.add(SystemDictConstant.NOT_DELETED);
        paramsList.add(SystemDictConstant.IS_FALSE);

        return this.getjdbcTemplate().queryForList(sql.toString(), paramsList.toArray());
    }


}
